package me.zhengjie.modules.website.repository;

import me.zhengjie.modules.website.domain.ContentTable;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * @author system
 * @since 2024-09-26 16:41:54
 */
@Repository
public interface ContentTableRepository extends JpaRepository<ContentTable, Long>, JpaSpecificationExecutor<ContentTable> {

    @Modifying
    @Query("UPDATE ContentTable c SET c.viewCount = c.viewCount + 1 WHERE c.contentId = :contentId")
    void incrementViewCount(@Param("contentId") Long contentId);

    @Modifying
    @Query("UPDATE ContentTable c SET c.likeCount = c.likeCount + 1 WHERE c.contentId = :contentId")
    void incrementLikeCount(@Param("contentId") Long contentId);

/*    @Query("SELECT c FROM ContentTable c WHERE c.status = 1 AND CONCAT(',', c.columnIds, ',') LIKE CONCAT('%', :columnId, '%') ORDER BY c.createTime DESC")
    Page<ContentTable> findByColumnIdInColumnIds(@Param("columnId") String columnId, Pageable pageable);*/

/*    @Query(value = "SELECT * FROM content_table c " +
            "WHERE c.column_ids LIKE %:columnIds% " +
            "AND c.status = 1 " +
            "ORDER BY c.sort_order ASC", nativeQuery = true)
    Page<ContentTable> findByColumnIdsLike(@Param("columnIds") String columnIds, Pageable pageable);*/

/*
    @Query(value = "SELECT * FROM content_table c " +
            "WHERE c.column_ids LIKE %:columnIds% " +
            "AND c.status = 1 " +
            "ORDER BY c.sort_order DESC, " +  // 按照 sort_order 降序排序，sort_order 越大越靠前
            "COALESCE(c.release_time, '1000-12-31 23:59:59') DESC",  // release_time 为 null 的排到最后，非 null 按照时间降序
            nativeQuery = true)
    Page<ContentTable> findByColumnIdsLike(@Param("columnIds") String columnIds, Pageable pageable);
*/


/*
    @Query(value = "SELECT * FROM content_table c " +
            "WHERE c.column_ids LIKE CONCAT('%', :columnIds, '%') " +
            "AND c.status = 1 " +
            "ORDER BY " +
            "CASE WHEN c.sort_order IS NOT NULL THEN c.sort_order ELSE -1 END DESC, " +
            "COALESCE(c.release_time, '1000-12-31 23:59:59') DESC, " +
            "c.id DESC",
            nativeQuery = true)
    Page<ContentTable> findByColumnIdsLike(@Param("columnIds") String columnIds, Pageable pageable);
*/

    @Query(value =
            "WITH ranked_content AS ( " +
                    "  SELECT *, " +
                    "         ROW_NUMBER() OVER ( " +
                    "             ORDER BY " +
                    "               CASE WHEN sort_order IS NOT NULL THEN sort_order ELSE -1 END DESC, " +
                    "               COALESCE(release_time, CAST('1000-12-31 23:59:59' AS timestamp)) DESC, " +
                    "               column_ids DESC " +
                    "         ) AS row_num " +
                    "  FROM content_table c " +
                    "  WHERE column_ids LIKE CONCAT('%', :columnIds, '%') " +
                    "    AND status = 1 " +
                    ") " +
                    "SELECT * FROM ranked_content " +
                    "WHERE row_num > :startRow AND row_num <= :endRow",
            nativeQuery = true)
    List<ContentTable> findByColumnIdsWithRowNumber(@Param("columnIds") String columnIds,
                                                    @Param("startRow") int startRow,
                                                    @Param("endRow") int endRow);

    @Query(value = "SELECT count(1) FROM content_table c WHERE column_ids LIKE CONCAT('%', :columnIds, '%') AND status = 1",
            nativeQuery = true)
    long countByColumnIdsLike(@Param("columnIds") String columnIds);


    @Query(value = "SELECT * FROM content_table c " +
            "WHERE c.column_ids IN :columnIds " +
            "AND c.status = 1 " +
            "ORDER BY " +
            "CASE WHEN c.sort_order IS NOT NULL THEN c.sort_order ELSE -1 END DESC, " +
            "COALESCE(c.release_time, '1000-12-31 23:59:59') DESC",
            nativeQuery = true)
    Page<ContentTable> findByColumnIdsIn(@Param("columnIds") String[] columnIds, Pageable pageable);

/*
    @Query(value = "SELECT * FROM content_table c " +
            "WHERE c.status = 1 " +
            "AND c.column_ids LIKE %:columnId% " +
            "ORDER BY c.sort_order ASC, c.release_time DESC",
            nativeQuery = true)
    List<ContentTable> findByColumnIdLike(@Param("columnId") String columnId);
*/

    // Page<ContentTable> findByTitleContaining(String title, Pageable pageable);
    Page<ContentTable> findByTitleContainingAndStatus(String title, int status, Pageable pageable);

    @Query("SELECT c FROM ContentTable c WHERE c.columnIds LIKE %:columnId% AND c.status = 1")
    List<ContentTable> findByColumnIdAndStatus(@Param("columnId") Long columnId);

    @Query("SELECT c FROM ContentTable c WHERE " +
            "(c.columnIds LIKE CONCAT('%,', :columnId, ',%') " +
            "OR c.columnIds LIKE CONCAT(:columnId, ',%') " +
            "OR c.columnIds LIKE CONCAT('%,', :columnId) " +
            "OR c.columnIds = :columnId) " +
            "AND c.title LIKE CONCAT('%', :title, '%')")
    List<ContentTable> findByColumnIdsAndTitle(@Param("columnId") String columnId, @Param("title") String title);

    @Query("SELECT c FROM ContentTable c WHERE " +
            "(c.columnIds LIKE CONCAT('%,', :columnId, ',%') " +
            "OR c.columnIds LIKE CONCAT(:columnId, ',%') " +
            "OR c.columnIds LIKE CONCAT('%,', :columnId) " +
            "OR c.columnIds = :columnId) " +
            "AND c.title LIKE CONCAT('%', :title, '%')")
    Page<ContentTable> findByColumnIdsAndTitleWithPagination(
            @Param("columnId") String columnId,
            @Param("title") String title,
            Pageable pageable);


    @Query(value = "SELECT * FROM content_table c " +
            "WHERE c.column_ids LIKE %:columnIds% " +
            "AND c.status = 1",
            countQuery = "SELECT count(*) FROM content_table c " +
                    "WHERE c.column_ids LIKE %:columnIds% " +
                    "AND c.status = 1",
            nativeQuery = true)
    Page<ContentTable> findByColumnIdsLikeToTile(@Param("columnIds") String columnIds, Pageable pageable);

}
